﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DTO;

namespace DAO
{
    public class PhieuXetNghiemDAO
    {
        public static DataTable LayDanhSachPhieuXetNghiem()
        {
            string sql;
            sql = "SELECT * FROM [PhieuXetNghiem]";
            return DataAccess.ExecQuery(sql);
        }

        public static int ThemPhieuXetNghiem(PhieuXetNghiemDTO obj)
        {
            string sql = "";
            sql = string.Format(@"INSERT INTO [PhieuXetNghiem](NgayXN, NhanXetKetQuaXN, MaPKB) VALUES(N'{0}', N'{1}', {2})",
                                                obj.NgayXN, obj.NhanXetKetQuaXN, obj.MaPKB);
            DataAccess.ExecNonQuery(sql);

            sql = "select max(MaPXN) from PhieuXetNghiem";
            DataTable dt = DataAccess.ExecQuery(sql);
            int max = Convert.ToInt32(dt.Rows[0][0]);
            return max;
        }

        public static void SuaPhieuXetNghiem(PhieuXetNghiemDTO obj)
        {
            string sql = "";
            sql = string.Format(@"UPDATE [PhieuXetNghiem]
                                SET NgayXN = N'{0}', NhanXetKetQuaXN = N'{1}', MaPKB = {2}
                                WHERE MaPXN = {4}", obj.NgayXN, obj.NhanXetKetQuaXN, obj.MaPKB, obj.MaPXN);
            DataAccess.ExecNonQuery(sql);
        }

        public static void XoaPhieuXetNghiem(int maPhieuXetNghiem)
        {
            string sql;
            sql = string.Format("DELETE [PhieuXetNghiem] WHERE MaPXN = {0}", maPhieuXetNghiem);
            DataAccess.ExecNonQuery(sql);
        }

        public static PhieuXetNghiemDTO LayThongTinPhieuXetNghiemTheoMaPhieuXetNghiem(int maPhieuXetNghiem)
        {
            PhieuXetNghiemDTO kq = new PhieuXetNghiemDTO();
            string sql;
            sql = @"select * FROM [PhieuXetNghiem]
                    where MaPXN = " + maPhieuXetNghiem;
            DataTable dt = DataAccess.ExecQuery(sql);
            if (dt != null && dt.Rows.Count > 0)
            {
                kq.MaPXN = Convert.ToInt32(dt.Rows[0]["MaPXN"]);
                kq.NgayXN = Convert.ToDateTime(dt.Rows[0]["NgayXN"]);
                kq.NhanXetKetQuaXN = Convert.ToString(dt.Rows[0]["NhanXetKetQuaXN"]);
                kq.MaPKB = Convert.ToInt32(dt.Rows[0]["MaPKB"]);
            }
            return kq;
        }
    }
}
